package net.wofly.right.util;

import net.wofly.right.dto.CustomDto;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.commons.CommonsMultipartFile;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;


public class ReadExcel {
    //总行数
    private int totalRows = 0;  
    //总条数
    private int totalCells = 0; 
    //错误信息接收器
    private String errorMsg;
    //构造方法
    public ReadExcel(){}
    //获取总行数
    public int getTotalRows()  { return totalRows;} 
    //获取总列数
    public int getTotalCells() {  return totalCells;} 
    //获取错误信息
    public String getErrorInfo() { return errorMsg; }  
    
  /**
   * 验证EXCEL文件
   * @param filePath
   * @return
   */
  public boolean validateExcel(String filePath){
        if (filePath == null || !(WDWUtil.isExcel2003(filePath) || WDWUtil.isExcel2007(filePath))){  
            errorMsg = "文件名不是excel格式";  
            return false;  
        }  
        return true;
  }
    
  /**
   * 读EXCEL文件，获取客户信息集合
   * @param fileName
   * @param mFile
   * @return
   */
  public List<CustomDto> getExcelInfo(String fileName, MultipartFile mFile, String userID){
       //初始化客户信息的集合
       List<CustomDto> customerList=new ArrayList<>();
       //初始化输入流
       InputStream is = null;  
       try{
          //验证文件名是否合格
          if(!validateExcel(fileName)){
              return null;
          }
          //根据文件名判断文件是2003版本还是2007版本
          boolean isExcel2003 = true; 
          if(WDWUtil.isExcel2007(fileName)){
              isExcel2003 = false;  
          }

          //根据新建的文件实例化输入流
          is = mFile.getInputStream();
          //根据excel里面的内容读取客户信息
          customerList = getExcelInfo(is, isExcel2003, userID);
          is.close();
      }catch(Exception e){
          e.printStackTrace();
      } finally{
          if(is !=null)
          {
              try{
                  is.close();
              }catch(IOException e){
                  is = null;    
                  e.printStackTrace();  
              }
          }
      }
      return customerList;
  }
  /**
   * 根据excel里面的内容读取客户信息
   * @param is 输入流
   * @param isExcel2003 excel是2003还是2007版本
   * @return
   * @throws IOException
   */
  public  List<CustomDto> getExcelInfo(InputStream is,boolean isExcel2003, String userID){
       List<CustomDto> customerList=null;
       try{
           /** 根据版本选择创建Workbook的方式 */
           Workbook wb = null;
           //当excel是2003时
           if(isExcel2003){
               wb = new HSSFWorkbook(is); 
           }
           else{//当excel是2007时
               wb = new XSSFWorkbook(is); 
           }
           //读取Excel里面客户的信息
           customerList=readExcelValue(wb, userID);
       }
       catch (IOException e)  {  
           e.printStackTrace();  
       }  
       return customerList;
  }
  /**
   * 读取Excel里面客户的信息
   * @param wb
   * @return
   */
  private List<CustomDto> readExcelValue(Workbook wb, String userID){
      //得到第一个shell  
       Sheet sheet=wb.getSheetAt(0);
       
      //得到Excel的行数
       this.totalRows=sheet.getPhysicalNumberOfRows();
       
      //得到Excel的列数(前提是有行数)
       if(totalRows>=1 && sheet.getRow(0) != null){
            this.totalCells=sheet.getRow(0).getPhysicalNumberOfCells();
       }
       
       List<CustomDto> customerList=new ArrayList<CustomDto>();
      CustomDto customer;
      //循环Excel行数,从第二行开始。标题不入库
       outer: for(int r=2;r<totalRows;r++){
           Row row = sheet.getRow(r);
           if (row == null) continue;
           customer = new CustomDto();
           
           //循环Excel的列
           for(int c = 0; c <this.totalCells; c++){    
               Cell cell = row.getCell(c);
               if (null != cell){
                   if(c==0){
                       customer.setCustomName(cell.getStringCellValue());
                   }else if(c==1){
                       String organizationTypeStr = cell.getStringCellValue();
                       if (organizationTypeStr != null && organizationTypeStr.trim().length() > 0) {
                           RoleEnum roleEnum = RoleEnum.valueOf(organizationTypeStr.trim());
                           if (roleEnum != null) {
                               customer.setOrganizationType(roleEnum.getValue());
                               customer.setOrganizationTypeStr(organizationTypeStr.trim());
                           }
                       }
                   }else if(c==2){
                       customer.setAddr(cell.getStringCellValue());
                   }else if(c==3){
                       customer.setLinkman(cell.getStringCellValue());
                   }else if(c==4){
                       if (StringUtils.isBlank(cell.getStringCellValue())) {
                           break outer;
                       }
                       customer.setMobile(cell.getStringCellValue());
                   }else if(c==5){
                       customer.setBusinessArea(cell.getStringCellValue());
                   }else if(c==6){
                   }else if(c==7){
                       customer.setDiningTable(Double.valueOf(cell.getNumericCellValue()).intValue());
                   }else if(c==8){
                       customer.setCassetteDeck(Double.valueOf(cell.getNumericCellValue()).intValue());
                   }else if(c==9){
                       customer.setBigBox(Double.valueOf(cell.getNumericCellValue()).intValue());
                   }else if(c==10){
                       customer.setMidBox(Double.valueOf(cell.getNumericCellValue()).intValue());
                   }else if(c==11){
                       customer.setMinBox(Double.valueOf(cell.getNumericCellValue()).intValue());
                   }else if(c==12){
                       customer.setCurrCooperationSituation(cell.getStringCellValue());
                   }else if(c==13){
                       customer.setFive(strToInt(cell.getStringCellValue()));
                   }else if(c==14){
                       customer.setSix(strToInt(cell.getStringCellValue()));
                   }else if(c==15){
                       customer.setTen(strToInt(cell.getStringCellValue()));
                   }else if(c==16){
                       customer.setTwenty(strToInt(cell.getStringCellValue()));
                   }else if(c==17){
                       customer.setRedBeautiful(strToInt(cell.getStringCellValue()));
                   }else if(c==18){
                       customer.setSilverBeautiful(strToInt(cell.getStringCellValue()));
                   }else if(c==19){
                       customer.setRemark1(strToInt(cell.getStringCellValue()));
                   }else if(c==20){
                       customer.setRemark2(strToInt(cell.getStringCellValue()));
                   }else if(c==21){
                       customer.setRemark3(strToInt(cell.getStringCellValue()));
                   }else if(c==22){
                       customer.setRemark4(strToInt(cell.getStringCellValue()));
                   }else if(c==23){
                       customer.setCompetitorCooperationForm(cell.getStringCellValue());
                   }else if(c==24){
                       customer.setCompetitorProductAndPrise(cell.getStringCellValue());
                   }else if(c==25){
                       customer.setDescp(cell.getStringCellValue());
                   }else if(c==26){
                       customer.setFollowUserID(userID);
                       customer.setOptUserID(userID);
                   }
               }
           }
           //添加客户
           customerList.add(customer);
       }
       return customerList;
  }

  private int strToInt(String strVal){
      if (StringUtils.isBlank(strVal) || "无".equals(strVal.trim())) {
          return 0;
      } else if ("有".equals(strVal.trim())) {
          return 1;
      }
      return 0;
  }


}